/*
   -------FAB DAO By Raj Vallamsetla------
   -------Created on 5/15/2002------------

	getFabName(String productId)
	comments : productId = BaseProductId

	getPartNumber(String productId,String fabName)
	comments : This method gets the fabname from product table
			   PartNumber is nothing but the fab name
*/

package com.scalar.chiptrack.operations.process.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.VendorLocation;
import com.scalar.chiptrack.operations.process.FabStageGroup;
import com.scalar.chiptrack.operations.process.forms.FabForm;
import com.scalar.chiptrack.operations.process.forms.FabProcessRecipeForm;
import com.scalar.chiptrack.utils.DBLoader;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import org.apache.struts.util.LabelValueBean;

public class FabDAO
{

 private static DBLoader s_dbLoader = null;


     public FabDAO()
	  {

	  }

	public static FabForm loadFabProcessData (String processStepId) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        FabForm fabData = null;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.fab.loadfabData"));
            pstmt.setString (1, processStepId);

            rset = pstmt.executeQuery();

            if (rset.next()) {
                fabData = new FabForm ();

				fabData.setLotHoldYieldLimit(rset.getFloat("FT_Lot_Hold_Yield_Limit"));
				fabData.setStandardUnitCost(rset.getFloat("FT_Standard_Unit_Cost"));
				fabData.setGross(rset.getInt("FT_Gross_Die_Wafer"));
				fabData.setPoFrequency(rset.getString("FT_PO_Frequency"));
				fabData.setLotScrapYieldLimit(rset.getFloat("FT_Lot_Scrap_Yield_Limit"));
				fabData.setTargetYield(rset.getFloat("FT_Target_Yield"));
				fabData.setProcDocNo(rset.getString("Procedure_Doc_Number"));
				fabData.setMaskIdNo(rset.getString("Mask_ID_Number"));
				fabData.setFabName(rset.getString("Part_Number"));
				fabData.setProductId(rset.getString("P_Parent_Product_ID"));
				fabData.setComments(rset.getString("PS_Comments"));
            }
            if( fabData != null )
            {
            	fabData.setDupProductName(ProductDao.loadProductDupNames( Integer.parseInt(processStepId) ));
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Access Fab Data From database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return fabData;
    }


	public static ArrayList loadRecipeData (String processStepId) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        FabProcessRecipeForm fabProcessRecipeForm = null;
		ArrayList PCMTargetList = new ArrayList();

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.fab.loadrecipeData"));
            pstmt.setString (1, processStepId);
            rset = pstmt.executeQuery();

            while (rset.next()) {
                fabProcessRecipeForm = new FabProcessRecipeForm ();
				fabProcessRecipeForm.setRecipeId(rset.getString("PCMTarget_ID"));
				fabProcessRecipeForm.setParameter(rset.getString("Parameter_Name"));
				fabProcessRecipeForm.setMinValue(rset.getString("Min_Value"));
				fabProcessRecipeForm.setMaxValue(rset.getString("Max_Value"));
				PCMTargetList.add(fabProcessRecipeForm);

            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Access Recipe Data Data From database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return PCMTargetList;
    }



	/*public String getFabName(String productId) throws ScalarException  {

				 Connection conn = null;
				 ResultSet rs    = null;
				 PreparedStatement pstmt=null;
				 String pageId="";
				 String fabName="";

    FabProcessSplitDAO fabProcessSplitDao = new FabProcessSplitDAO();

	pageId=fabProcessSplitDao.getPageId("FabProcessSetUp",productId);

	ArrayList fabNames = new ArrayList();

		try{


         conn = DataSource.getConnection();
		 pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.product.fabName"));

		 pstmt.setString(1,pageId);


		 rs =  pstmt.executeQuery();

			while(rs.next())
			{
				fabName=rs.getString(1);
				fabNames.add(fabName);

			}

			for(int i=0; i<fabNames.size();i++){

			String partNumber = getPartNumber(productId,(String)fabNames.get(i));
			System.out.println("partNumber "+partNumber);

             if(partNumber.equals("")){
               fabName =(String)fabNames.get(i);
               i=fabNames.size();
			 }

			}

		 }

	    	 catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get Fab Name from database", sqle);
        }


            finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

				return fabName;

        } */



		public String getPartNumber(String productId,String fabName) throws ScalarException{

				 Connection conn = null;
				 ResultSet rs    = null;
				 PreparedStatement pstmt=null;

				 String partNumber="";

		try{

		    conn = DataSource.getConnection();
			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.product.partNumber"));
			pstmt.setString(1,productId);
			pstmt.setString(2,fabName);
	    	rs =  pstmt.executeQuery();
			while(rs.next()){
			partNumber=rs.getString(1);
			}

			}

	    	catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get Part Number from database", sqle);
			}


            finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
			}

				return partNumber;

        }


		public static ArrayList getFabStage(int processStepId ) throws ScalarException{

				 Connection conn = null;
				 ResultSet rs    = null;
				 PreparedStatement pstmt=null;

				 ArrayList fabStageList = new ArrayList();

		try{

		 conn = DataSource.getConnection();
		 pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.product.fabStage"));

		 pstmt.setInt(1,processStepId);
		 rs =  pstmt.executeQuery();

		 FabStage fabStage = null;

			while(rs.next())
			{
				fabStage = new FabStage();
				fabStage.setStageId(rs.getInt("Stage_ID"));
				fabStage.setOrder(rs.getInt("PST_Order"));
				fabStage.setFabStageName(rs.getString("FST_Stage_Name"));
				fabStage.setProcedureDoc(rs.getInt("ProcedureDoc_ID"));
				fabStage.setComments(rs.getString("FST_Comments"));
				fabStage.setSubStageId(rs.getInt("SubStage_ID"));
				fabStage.setType("exists");
				fabStageList.add(fabStage);
			}

		 }

	    	catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get Fab Stages from database", sqle);
			}

			finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
			}
				return fabStageList;

        }

	/* Method Over Loaded for the sake of
	getting the FabStages for the critiria of location and ProcessStepId

	@ author Raj Vallamsetla */


public static ArrayList getFabStage(int processStepId,int locationId ) throws ScalarException{

				 Connection conn = null;
				 ResultSet rs    = null;
				 PreparedStatement pstmt=null;

				 ArrayList fabStageList = new ArrayList();



		try{

		 conn = DataSource.getConnection();
		 pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.product.fabStageAndLoc"));

		 pstmt.setInt(1,processStepId);
		 pstmt.setInt(2,locationId);

		 rs =  pstmt.executeQuery();

		 FabStage fabStage = null;

			while(rs.next())
			{
				fabStage = new FabStage();
				fabStage.setStageId(rs.getInt(1));
				fabStage.setFabStageName(rs.getString(2));
				fabStage.setSubStageId(rs.getInt(3));
				fabStage.setOrder(rs.getInt(4));
				fabStage.setComments(rs.getString(5));

				fabStageList.add(fabStage);
			}

		 }

	    	catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get Fab Stages from database", sqle);
			}

			finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
			}

				return fabStageList;

        }


    //Ends Here



		public static ArrayList getFabSubStage(String stageId ) throws ScalarException{

				 Connection conn = null;
				 ResultSet rs    = null;
				 PreparedStatement pstmt=null;

				 ArrayList fabSubStageList = new ArrayList();



		try{

		 conn = DataSource.getConnection();
		 pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.product.fabSubStage"));

		 pstmt.setString(1,stageId);
		 rs =  pstmt.executeQuery();

		 FabSubStage fabSubStage = null;

			while(rs.next())
			{
				fabSubStage = new FabSubStage();
				fabSubStage.setSubStageId(rs.getString("SubStage_ID"));
				fabSubStage.setFabStepName(rs.getString("FSST_Sub_Stage_Name"));
				fabSubStage.setProcedureDoc(rs.getInt("ProcedureDoc_ID"));
				fabSubStage.setComments(rs.getString("FSST_Comments"));
				fabSubStageList.add(fabSubStage);
			}

		 }

	    	catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get Fab Sub Stages from database", sqle);
			}

			finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
			}

				return fabSubStageList;

        }


		public static ArrayList getFabStages(int processStepId,int locationId ) throws ScalarException{


				 ArrayList fabStageList				= new ArrayList();
				 ArrayList fabSubStageList			= new ArrayList();
				 ArrayList fabStageSubStageList		= new ArrayList();
				 FabStage fabStage					= null;
				 FabSubStage fabSubStage			= null;

		try{
				fabStageList = FabDAO.getFabStage(processStepId,locationId);
			    String order = "0";


			for (int i=0;i<fabStageList.size();i++)
			{
				fabStage =(FabStage)fabStageList.get(i);
				order =new Integer(fabStage.getOrder()).toString();
				fabStage.setSequence(order);
				fabStage.setType("stage");
				fabStageSubStageList.add(fabStage);

				if(fabStage.getSubStageId()!=0){
				fabSubStageList=FabDAO.getFabSubStage(new Integer(fabStage.getStageId()).toString());

				for (int j=0;j<fabSubStageList.size();j++)
				{
					fabSubStage =(FabSubStage)fabSubStageList.get(j);
					fabStage = new FabStage();
					fabStage.setFabStageName(fabSubStage.getFabStepName());
					fabStage.setComments(fabSubStage.getComments());
					fabStage.setStageId(new Integer(fabSubStage.getSubStageId()).intValue());
					fabStage.setType("substage");
					int count =j+1;
					fabStage.setSequence(order+"-"+count);
					fabStageSubStageList.add(fabStage);

				}

			}

			}

		 }

	    	catch (Exception e) {
            e.printStackTrace();
            throw new ScalarException ("Could not get fab Stages ", e);
			}

				return fabStageSubStageList;

        }

	public void saveFab( DBTransaction dbTransaction, int processStepId, float lotHoldYieldLimit, float standardUnitCost, int gross, String poFrequency, float lotScrapYieldLimit, float targetYield, int procedureDocId, int maskId ) throws ScalarException
	{
		saveFab( dbTransaction, processStepId, lotHoldYieldLimit, standardUnitCost, gross, poFrequency, lotScrapYieldLimit, targetYield, procedureDocId, maskId, 0 );
	}

	public void saveFab( DBTransaction dbTransaction, int processStepId, float lotHoldYieldLimit, float standardUnitCost, int gross, String poFrequency, float lotScrapYieldLimit, float targetYield, int procedureDocId, int maskId, int dieSize ) throws ScalarException
	{
		Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt=null;
		boolean status = false;
		String partNumber="";

		try
		{
	        if (dbTransaction == null) 
	        {
            	conn = DataSource.getConnection();
            }
            else 
            {
            	conn = dbTransaction.getConnection();
            }

		 	pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.fab"));

			pstmt.setInt	( 1, processStepId );
			pstmt.setFloat	( 2, lotHoldYieldLimit );
		 	pstmt.setFloat	( 3, standardUnitCost );
		 	pstmt.setInt	( 4, gross );
		 	pstmt.setString	( 5, poFrequency );
		 	pstmt.setFloat	( 6, lotScrapYieldLimit );
		 	pstmt.setFloat	( 7, targetYield );
		 	pstmt.setInt	( 8, procedureDocId );
		 	pstmt.setInt	( 9, maskId );
			pstmt.setInt	( 10, dieSize );
			
			pstmt.executeUpdate();
		 	status = true;
		}
		catch(SQLException sql){
			sql.printStackTrace();
			throw new ScalarException("Could not Save Fab Data ", sql);
		}finally{
			// Commit/Rollback here it selef -- if its not in a manual transaction
	        if (dbTransaction == null) {
    	        try {
        	        if (conn != null) {
            	        if (status == true) {
                	        conn.commit();
                        }
                        else {
                    	    conn.rollback();
                        }
					}
				}
                catch (SQLException ignore) {}
			}
			if(pstmt != null){
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close(); }catch(Exception e){}
			}
		}
	}


	public static int saveFabStage(DBTransaction dbTransaction,int processStepID, FabStage fabStage) throws ScalarException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int stageId = 0;
		boolean status = false;

		try{

			if (dbTransaction == null) {
                            conn = DataSource.getConnection();
                        }
                        else {
                            conn = dbTransaction.getConnection();
                        }


			pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.fab.insertFabStage"));


				pstmt.setInt(1, processStepID);
				pstmt.setInt(2, fabStage.getOrder());
				pstmt.setString(3, fabStage.getFabStageName());
				pstmt.setNull(4, java.sql.Types.INTEGER);
				pstmt.setString(5, fabStage.getComments());

				pstmt.executeUpdate();

			status = true;

			pstmt.close();
			pstmt=null;
			pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.upload.readIdentity"));
			rs = pstmt.executeQuery();

			if(rs.next() != false){
				stageId = rs.getInt("docID");
			}




		}catch(SQLException sql){
			sql.printStackTrace();
			throw new ScalarException(" Save Fab Stage Failed", sql);

		}finally{

			// Commit/Rollback here it selef -- if its not in a manual transaction
                        if (dbTransaction == null) {
                            try {
                                if (conn != null) {
                                    if (status == true) {
                                        conn.commit();
                                    }
                                    else {
                                        conn.rollback();
                                    }
                                }
                            }
                            catch (SQLException ignore) {}
                        }

			if(pstmt != null){
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close(); }catch(Exception e){}
			}

		}

		return stageId;

	}


	public static void saveFabSubStage(DBTransaction dbTransaction,int stageId, Iterator fabSubStageList) throws ScalarException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean status = false;
		int subStageId=0;

		try{

			if (dbTransaction == null) {
                            conn = DataSource.getConnection();
                        }
                        else {
                            conn = dbTransaction.getConnection();
                        }


			pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.fab.insertFabSubStage"));

			FabSubStage fabSubStage = null;

			while(fabSubStageList.hasNext()){
				fabSubStage = (FabSubStage)fabSubStageList.next();
				pstmt.setInt(1, stageId);
				pstmt.setString(2, fabSubStage.getFabStepName());
				pstmt.setNull(3, java.sql.Types.INTEGER);
				pstmt.setString(4, fabSubStage.getComments());

				pstmt.addBatch();

			}
			pstmt.executeBatch();


			pstmt.close();
			pstmt=null;
			pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.upload.readIdentity"));
			rs = pstmt.executeQuery();

			if(rs.next() != false){
				subStageId = rs.getInt("docID");
			}

			pstmt.close();
			pstmt=null;
			pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.fab.updateFabStageId"));
			pstmt.setInt(1,subStageId);
			pstmt.setInt(2,stageId);
			pstmt.executeUpdate();


			status = true;


		}catch(SQLException sql){
			sql.printStackTrace();
			throw new ScalarException(" Save saveFabSubStage Failed", sql);

		}finally{

			// Commit/Rollback here it selef -- if its not in a manual transaction
                        if (dbTransaction == null) {
                            try {
                                if (conn != null) {
                                    if (status == true) {
                                        conn.commit();
                                    }
                                    else {
                                        conn.rollback();
                                    }
                                }
                            }
                            catch (SQLException ignore) {}
                        }

			if(pstmt != null){
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close(); }catch(Exception e){}
			}

		}

	}

    /**
     * @param processStepId
     */
    public void saveFabMap(DBTransaction dbTransaction,int processStepId, String[] partNumbers, ArrayList vendorLocationList)throws ScalarException
    {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean status = false;
		VendorLocation vendorLocation = null;
		try
		{
			if (dbTransaction == null)
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

			pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.fab.deleteFromFabMap"));
			pstmt.setInt(1,processStepId);
			pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;

			pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.fab.insertFabMap"));
			//System.out.println("Partnumbers List Size "+  partNumbers.length);
			for (int y=0 ; y < vendorLocationList.size() ; y++)
			{
				for( int i=0 ; i < partNumbers.length ; i++ )
				{
					vendorLocation = (VendorLocation)vendorLocationList.get(y);
					pstmt.setInt(1,processStepId);
					pstmt.setString(2,partNumbers[i]);
					pstmt.setInt(3,vendorLocation.getLocationIDAsInt());
					pstmt.addBatch();
				}
			}
			pstmt.executeBatch();
			pstmt.close();
			pstmt=null;
			status = true;
		}
		catch(SQLException sql)
		{
			sql.printStackTrace();
			throw new ScalarException(" Save saveFabMap Failed", sql);
		}
		finally
		{
		// Commit/Rollback here it selef -- if its not in a manual transaction
			if (dbTransaction == null) {
				try {
					if (conn != null) {
						if (status == true) {
							conn.commit();
						}
						else {
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}

			if(pstmt != null){
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close(); }catch(Exception e){}
			}
    	}
    }

    /**
     * @param processStepId
     * @return DupFab names
     */
    public static String loadFabDupNames(String processStepId,String fabName) throws ScalarException
    {
		Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt = null;
		StringBuffer buff = new StringBuffer();

	   	try
	   	{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.fab.selectDupNamesFromFabMap"));

			pstmt.setInt(1,Integer.parseInt(processStepId));
			rs =  pstmt.executeQuery();

		   	while( rs.next() )
		   	{
		   		if(!rs.getString("Part_Number").equals(fabName))
		   		{
		   			buff.append( rs.getString("Part_Number")+";") ;
		   		}
			}
		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
		   	throw new ScalarException ("Could not get Fab Stages from database", sqle);
		}
		finally
		{
		   if (rs != null) {
			   try { rs.close(); } catch (SQLException ignore) {}
		   }
		   if (pstmt != null) {
			   try { pstmt.close(); } catch (SQLException ignore) {}
		   }
		   if (conn != null) {
			   try { conn.close(); } catch (SQLException ignore) {}
		   }
		}
		if( buff.length() > 0 )
			return buff.toString().substring(0,buff.toString().length()-1);
		else
			return buff.toString();
    }

    /**
     * @param partNumbers
     */
    public static String checkDupInProductMapTable(String[] partNumbers, int processStepId)throws ScalarException
    {
		Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt = null;
		StringBuffer buff = new StringBuffer();
		String result = "";

		for( int i=0; i < partNumbers.length; i++ )
		{

			buff.append("'"+partNumbers[i].replaceAll("'","''")+"',");
		}

		try
		{
			conn = DataSource.getConnection();
			if( processStepId == -1)
			{
				pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.fab.checkDupNamesFromFabMap")+"("+ buff.toString().substring( 0 , buff.length()-1) +")");
			}
			else
			{
				pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.fab.checkDupNamesFromFabMapWithProcessStepID")+"("+ buff.toString().substring( 0 , buff.length()-1) +")");
				pstmt.setInt( 1 , processStepId );
			}

			rs =  pstmt.executeQuery();

			while( rs.next() )
			{
				result = result+rs.getString("Part_Number")+",";
			}
		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not get check Duplicate Fab Part Numbers from database", sqle);
		}
		finally
		{
		   if (rs != null) {
			   try { rs.close(); } catch (SQLException ignore) {}
		   }
		   if (pstmt != null) {
			   try { pstmt.close(); } catch (SQLException ignore) {}
		   }
		   if (conn != null) {
			   try { conn.close(); } catch (SQLException ignore) {}
		   }
		}
		if( result.length() > 0 )
		{
			return result.substring( 0 , result.length()-1 );
		}
		else
		{
			return result;
		}

    }

    public static ArrayList getFabLotNumbers(String processStepId) throws ScalarException{

	    Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean status = false;
		ArrayList list = null;

		try{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.fab.getFabLotNumbers"));
			pstmt.setString(1, processStepId);
			rs = pstmt.executeQuery();
			if(rs != null){
            	list = new ArrayList();
            }
            while(rs.next()){
            	ArrayList tempList = new ArrayList();
            	tempList.add(rs.getString("Lot_Number"));
            	tempList.add(rs.getString("WAT"));
            	tempList.add(rs.getString("WSM"));
            	tempList.add(rs.getString("QCI"));
            	list.add(tempList);
            }
		}catch(SQLException sql){
			sql.printStackTrace();
			throw new ScalarException(" getAllFabPartNumbers() in FabDAO failed", sql);
		}finally{
            if(rs != null){
            	try{ rs.close();}catch(Exception e){}
            }
			if(pstmt != null){
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close(); }catch(Exception e){}
			}
		}
		return list;
	}
	
	public void updateFab(DBTransaction dbTransaction, int processStepId, float lotHoldYeildLimit, float standardUnitCost, int gross, String poFrequency, float lotScrapYieldLimit, float targetYield, int procedureDocId, int maskId ) throws ScalarException
	{
		updateFab( dbTransaction, processStepId, lotHoldYeildLimit, standardUnitCost, gross, poFrequency, lotScrapYieldLimit, targetYield, procedureDocId, maskId, 0 );
	}

    /**
     * @param dbTransaction
     * @param processStepId
     * @param lotHoldYeildLimit
     * @param standardUnitCost
     * @param gross
     * @param poFrequency
     * @param lotScrapYieldLimit
     * @param targetYield
     * @param procedureDocId
     * @param maskId
     * @param dieSize
     */
    public void updateFab(DBTransaction dbTransaction, int processStepId, float lotHoldYeildLimit, float standardUnitCost, int gross, String poFrequency, float lotScrapYieldLimit, float targetYield, int procedureDocId, int maskId, int dieSize) throws ScalarException
    {
		 Connection conn = null;
		 ResultSet rs    = null;
		 PreparedStatement pstmt=null;
		 boolean status = false;
		 String partNumber="";

		try
		{
   			if (dbTransaction == null) 
   			{
				conn = DataSource.getConnection();
			}
			else 
			{
				conn = dbTransaction.getConnection();
			}

 			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.updateFab"));
 			pstmt.setFloat	( 1, lotHoldYeildLimit );
 			pstmt.setFloat	( 2, standardUnitCost );
 			pstmt.setInt	( 3, gross );
 			pstmt.setString	( 4, poFrequency );
 			pstmt.setFloat	( 5, lotScrapYieldLimit );
 			pstmt.setFloat	( 6, targetYield );
 			pstmt.setInt	( 7, procedureDocId );
 			pstmt.setInt	( 8, maskId );
 			pstmt.setInt	( 9, dieSize );
 			pstmt.setInt	( 10, processStepId );
 			
 			pstmt.executeUpdate();
 			status = true;
		}
		catch(SQLException sql){
			sql.printStackTrace();
			throw new ScalarException("Could not Save Fab Data ", sql);
		}finally
		{
			// Commit/Rollback here it selef -- if its not in a manual transaction
			if (dbTransaction == null) {
			try {
				if (conn != null) {
					if (status == true) {
						conn.commit();
					}
					else {
						conn.rollback();
					}
				}
			}
			catch (SQLException ignore) {}
			}
			if(pstmt != null){
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close(); }catch(Exception e){}
			}
		}
   	}

     /** To get the vendor names having the process type as Fab
     *
     * @return ArrayList
     * @throws ScalarException
     */

    public static ArrayList getFabVendorLocations() throws ScalarException
    {
        Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
        ArrayList locations = null;

	    try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get("operations.process.vendorLocation.FabLocations"));
            rs = pstmt.executeQuery();

            locations = new ArrayList();

            while ( rs.next() )
            {
                locations.add( new LabelValueBean( rs.getString("LOCATION_NAME"), rs.getInt("LOCATION_ID")+"" ) );
            }
        }
        catch(SQLException sql)
        {
			sql.printStackTrace();
			throw new ScalarException(" While get the Fab venodrs in FabDAO failed", sql);
		}
        finally
        {
            if(rs != null)
            {
            	try{ rs.close();}catch(Exception e){}
            }
			if(pstmt != null)
            {
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null)
            {
				try{ conn.close(); }catch(Exception e){}
			}
		}

        return locations;
   }


    /** To get all the the Part number having the process type as Fab based on locationId
     *
     * @param locationId
     * @return
     * @throws ScalarException
     */
    public static ArrayList getFabPartNumbers( int locationId ) throws ScalarException
    {
        Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
        ArrayList partNoList = null;

	    try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.process.vendorPart.FabPartNumbers" ) );
            pstmt.setInt( 1, locationId );
            rs = pstmt.executeQuery();

            partNoList = new ArrayList();

            while ( rs.next() )
            {
                partNoList.add( new LabelValueBean( rs.getString("PART_NUMBER"), rs.getString("PROCESSSTEP_ID") ) );
            }
        }
        catch(SQLException sql)
        {
			sql.printStackTrace();
			throw new ScalarException(" While get the Fab venodrs in FabDAO failed", sql);
		}
        finally
        {
            if(rs != null)
            {
            	try{ rs.close();}catch(Exception e){}
            }
			if(pstmt != null)
            {
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null)
            {
				try{ conn.close(); }catch(Exception e){}
			}
		}

        return partNoList;
    }
    /** To get The Fab Stage Names
     *
     * @param processStepID
     * @param fabStageName
     * @return
     * @throws ScalarException
     */
    public static boolean isFabStageNameExists(int processStepID, String fabStageName ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean found         = false;
        try
        {

           conn = DataSource.getConnection();

            pstmt = conn.prepareStatement("Select count(*) as stage_count from Fab_Stage_table where Processstep_id = ? and FST_Stage_Name = ?");
            pstmt.setInt(1, processStepID);
            pstmt.setString(2, fabStageName);
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                found = rset.getInt( "stage_count" ) > 0 ? true : false;
            }
        }
        catch( SQLException sql )
        {
            sql.printStackTrace();
            throw new ScalarException(" Getting Fab Stage Failed", sql);
        }

        finally
        {

            if( rset != null )
            {
            	try{ rset.close();}catch( Exception e ) {}
            }
            if( pstmt != null )
            {
                try{ pstmt.close(); }catch( Exception e ) {}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch( Exception e ) {}
            }
        }
        return found;
    }

    /** To Save The Fab Stage Names
     *
     * @param dbTransaction
     * @param processStepID
     * @param fabStageName
     * @throws ScalarException
     */
    public static void saveFabStageName(DBTransaction dbTransaction , int processStepID, String fabStageName) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean status          = false;

        try
        {
           if (dbTransaction == null)
   			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

            pstmt = conn.prepareStatement("insert into Fab_Stage_Table(Processstep_id,FST_Stage_Name,TimeStamp) values (?, ?,CURRENT_TIMESTAMP )");

            pstmt.setInt(1, processStepID);
            pstmt.setString(2, fabStageName);
            pstmt.executeUpdate();
            status = true;
        }
        catch( SQLException sql )
        {
            sql.printStackTrace();
            throw new ScalarException(" Saving Fab Stage Failed", sql);
        }

        finally
        {
            try
                {
                    if (conn != null)
                    {
                        if ( status == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore ) {}

            if(pstmt != null){
                try{ pstmt.close(); }catch(Exception e){}
            }
            if(conn != null){
                try{ conn.close(); }catch(Exception e){}
            }
        }
    }
       /**  To Insert the Group name and related Stage names
        *
        * @param fabStage
        * @return boolean
        * @throws ScalarException
        */
     public static boolean insertStageGroups(DBTransaction dbTransaction , FabStage fabStage ) throws ScalarException
     {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        boolean status          = false;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            int processStepId =  fabStage.getProcessStepId() ;
            int locationId    =  fabStage.getLocationId();
            int selectedStageId = 0;
            ArrayList groupFabStages = fabStage.getFabStages();
            String[] selectedStages = null;
            String groupName  = null;

            for (int i = 0; i < groupFabStages.size(); i++)
            {
                int groupId;
                boolean remove;
                FabStageGroup fabStageGroup = ( FabStageGroup ) groupFabStages.get(i);
                selectedStages = fabStageGroup.getSelectedStages();
                groupName = fabStageGroup.getGroupName();
                groupId   = fabStageGroup.getGroupId();
                remove    = fabStageGroup.isChecked();

                if ( remove == true )
                {
                    pstmt = conn.prepareStatement("DELETE from Fab_Group_Table WHERE Processstep_id = ? and Location_Id = ? and Group_Id = ? ");
                    pstmt.setInt ( 1 , processStepId );
                    pstmt.setInt ( 2 , locationId );
                    pstmt.setInt ( 3 , groupId );
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement("DELETE from Fab_Group_Stage_Table WHERE Group_Id = ? ");
                    pstmt.setInt ( 1 , groupId );
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;
                }
                else
                {
                    if ( groupId > 0 )
                    {
                        pstmt = conn.prepareStatement("UPDATE Fab_Group_Table SET Fgt_Group_Name = ? WHERE Processstep_id = ? and Location_Id = ? and Group_Id = ? ");
                        pstmt.setString ( 1 , groupName );
                        pstmt.setInt ( 2 , processStepId );
                        pstmt.setInt ( 3 , locationId );
                        pstmt.setInt ( 4 , groupId );
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;
                    }

                    if ( groupId == -1 )
                    {
                        pstmt = conn.prepareStatement("Insert into Fab_Group_Table (Processstep_id,Location_Id,Fgt_Group_Name,Timestamp) VALUES (?,?,?,CURRENT_TIMESTAMP)");
                        pstmt.setInt ( 1 , processStepId );
                        pstmt.setInt ( 2 , locationId );
                        pstmt.setString ( 3 , groupName );
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareStatement( "Select @@IDENTITY as Group_Id" );
                        rs = pstmt.executeQuery();
                        if( rs.next() )
                        {
                            groupId = rs.getInt( "Group_Id" );
                        }
                        status = true;
                        if ( dbTransaction == null )
                        {
                            if ( conn != null )
                            {
                                if ( status == true )
                                {
                                    conn.commit();
                                }
                                else
                                {
                                    conn.rollback();
                                }
                            }
                        }
                        rs.close();
                        rs = null;
                        pstmt.close();
                        pstmt = null;
                    }

                    pstmt = conn.prepareStatement("delete from Fab_Group_Stage_Table where Group_id = ? ");
                    pstmt.setInt ( 1 , groupId );
                    pstmt.executeUpdate();
                    status = true;
                    if ( dbTransaction == null )
                    {
                        if ( conn != null )
                        {
                            if ( status == true )
                            {
                                conn.commit();
                            }
                            else
                            {
                                conn.rollback();
                            }
                        }
                    }
                    pstmt.close();
                    pstmt = null;

                    if ( selectedStages != null && selectedStages.length > 0 )
                    {
                        pstmt = conn.prepareStatement("INSERT INTO Fab_Group_Stage_Table( Group_Id, Stage_Id, TimeStamp) VALUES (?,?,CURRENT_TIMESTAMP) ");
                        for (int j = 0; j < selectedStages.length; j++)
                        {
                            try
                            {
                                selectedStageId = Integer.parseInt(selectedStages[j] ) ;
                            }
                            catch ( NumberFormatException nfe)
                            {
                                selectedStageId = 0;
                            }
                            if ( selectedStageId > 0 && groupId > 0 )
                            {
                                pstmt.setInt ( 1 , groupId );
                                pstmt.setInt ( 2 , selectedStageId );
                                pstmt.addBatch();
                                pstmt.executeBatch();
                            }
                        }
                    }
                }
            }
            status = true;
        }
        catch( SQLException sql )
        {
            sql.printStackTrace();
            throw new ScalarException(" Exception while Saving Fab Stage Gruops Failed", sql);
        }

        finally
        {
            try
                {
                    if ( dbTransaction == null )
                    {
                        if ( conn != null )
                        {
                            if ( status == true )
                            {
                                conn.commit();
                            }
                            else
                            {
                                conn.rollback();
                            }
                        }
                    }
                }
                catch ( SQLException ignore ) {}
            if(rs != null){
                try{ rs.close(); }catch(Exception e){}
            }
            if(pstmt != null){
                try{ pstmt.close(); }catch(Exception e){}
            }
            if(conn != null){
                try{ conn.close(); }catch(Exception e){}
            }
        }
        return status;
    }

    public static ArrayList loadFabStageGroups( int processstepId ,int locationId ) throws ScalarException
    {
        Connection conn             = null;
	    PreparedStatement pstmt     = null;
	    ResultSet rs                = null;
        ArrayList groupnameList     = null;
        FabStageGroup fabStageGroup = null;

	    try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement ("select Group_Id ,FGT_Group_Name from Fab_Group_Table where processstep_id= ? and location_id = ?");
            pstmt.setInt( 1 , processstepId);
            pstmt.setInt( 2 , locationId );
            rs = pstmt.executeQuery();

            groupnameList = new ArrayList();

            while ( rs.next() )
            {
                fabStageGroup = new FabStageGroup();
				fabStageGroup.setGroupId( rs.getInt(1) );
				fabStageGroup.setGroupName ( rs.getString(2) ) ;
                fabStageGroup.setChecked(false);
                groupnameList.add( fabStageGroup );
            }
        }
        catch(SQLException sql)
        {
			sql.printStackTrace();
			throw new ScalarException(" While get the Fab venodrs in FabDAO failed", sql);
		}
        finally
        {
            if(rs != null)
            {
            	try{ rs.close();}catch(Exception e){}
            }
			if(pstmt != null)
            {
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null)
            {
				try{ conn.close(); }catch(Exception e){}
			}
		}

        return groupnameList;
   }

   public static ArrayList loadExistingFabGroupStages( DBTransaction dbTransaction, int groupId ) throws ScalarException
    {
        Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
        ArrayList selectedStagesList = null;

	    try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ("select a.stage_id, b.FST_stage_name from Fab_Group_Stage_table a,FAB_stage_table b where a.stage_id = b.stage_id and a.group_id = ? order by b.FST_stage_name ");
            pstmt.setInt( 1 , groupId );
            rs = pstmt.executeQuery();

            selectedStagesList = new ArrayList();

            while ( rs.next() )
            {
                selectedStagesList.add( new org.apache.struts.util.LabelValueBean ( rs.getString("FST_stage_name") , rs.getInt( "stage_id" )+"" ) );
            }
        }
        catch(SQLException sql)
        {
			sql.printStackTrace();
			throw new ScalarException(" While get the Fab venodrs in FabDAO failed", sql);
		}
        finally
        {
            if(rs != null)
            {
            	try{ rs.close();}catch(Exception e){}
            }
			if(pstmt != null)
            {
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null)
            {
				try{ conn.close(); }catch(Exception e){}
			}
		}

        return selectedStagesList;
   }
    public static ArrayList loadAvailableFabGroupStages( DBTransaction dbTransaction, int locationId, int processStepId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList availableStagesList = null;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ("SELECT FST_STAGE_NAME, STAGE_ID FROM FAB_STAGE_TABLE WHERE PROCESSSTEP_ID= ? AND STAGE_ID NOT IN ( SELECT STAGE_ID FROM FAB_GROUP_STAGE_TABLE WHERE GROUP_ID IN ( SELECT GROUP_ID FROM FAB_GROUP_TABLE WHERE  PROCESSSTEP_ID= ? AND LOCATION_ID = ? )) AND FST_STAGE_NAME IS NOT NULL ORDER BY FST_STAGE_NAME");
            pstmt.setInt( 1 , processStepId );
            pstmt.setInt( 2 , processStepId );
            pstmt.setInt( 3 , locationId );
            rs = pstmt.executeQuery();

            availableStagesList = new ArrayList();

            while ( rs.next() )
            {
                availableStagesList.add( new org.apache.struts.util.LabelValueBean ( rs.getString("FST_STAGE_NAME") , rs.getInt( "STAGE_ID" )+"" ) );
            }
        }
        catch(SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException(" While get the Fab venodrs in FabDAO failed", sql);
        }
        finally
        {
            if(rs != null)
            {
                try{ rs.close();}catch(Exception e){}
            }
            if(pstmt != null)
            {
                try{ pstmt.close(); }catch(Exception e){}
            }
            if(conn != null)
            {
                try{ conn.close(); }catch(Exception e){}
            }
        }
        return availableStagesList;
   }

    /**
    *  Method to convert to Stages Array into Stages List( contains LabelValueBean objects)
    * @param stagesArray
    * @return
    * @throws ScalarException
    */
   public static ArrayList convertPartsArrayToList ( String[] stagesArray ) throws ScalarException
   {
       ArrayList stagesList = new ArrayList();
       try
       {
            for ( int i = 0; i < stagesArray.length; i++)
            {
                int stageId = Integer.parseInt( stagesArray[i] );
                stagesList.add( new LabelValueBean ( getStageName( stageId ) , stageId+"" ) );
            }
       }
       catch( Exception e)
       {
            e.printStackTrace();
       }
       return stagesList;
   }

   /**
    *   Method to get the Stage Name for the given stageId
    */
   public static String getStageName( int stageId ) throws ScalarException
   {
        Connection conn          = null;
        PreparedStatement pstmt  = null;
        ResultSet rs             = null;
        String stageName         = null;

        try
        {
           conn = DataSource.getConnection();

           pstmt   = conn.prepareStatement("SELECT FST_STAGE_NAME FROM FAB_STAGE_TABLE WHERE STAGE_ID = ?");
           pstmt.setInt( 1, stageId );
           rs = pstmt.executeQuery();

           while ( rs.next() )
           {
               stageName = rs.getString("FST_STAGE_NAME");
           }
        }
        catch(SQLException sql)
        {
           sql.printStackTrace();
           throw new ScalarException("Exception while loading the Fab Stage Name", sql);
        }
        finally
        {
           if(rs != null)
           {
               try{ rs.close();}catch(Exception e){}
           }
           if(pstmt != null)
           {
               try{ pstmt.close(); }catch(Exception e){}
           }
           if(conn != null)
           {
               try{ conn.close(); }catch(Exception e){}
           }
        }
        return stageName;
    }

}